#!/bin/bash
#工具名   : ogd (openGauss diagnostic) openGauss诊断器
#工具用途 : 可以通过该工具快捷输出诊断信息
#作者     ：zhangzhijing
#时间     ：2022-8-11
    
#
# default argments
#
PORT=$PGPORT
GAUSSDATA=$GAUSSDATA
XLOGDATA=$GAUSSDATA/pg_xlog
mode=release
nic=enp125s0f0


#####################################################################
#函数名    ：menu
#功能描述  ：打印功能列表
#输入      ：help（全量列表）、cpu、mem、cluster等子项列表
#输出      ：对应输入诊断项的使用方式
#返回      ：执行结束返回 1，重新从main函数进入
#####################################################################
function menu()
{
    echo -e "\tformat : ogd [arguments]"
    
    if [ "$1" = "help" ]; then
        echo "
        使用前提：数据库相关环境变量均配置完整。
        使用权限：仅限于在数据库个人用户XXX下使用。
    
        覆盖范围：
        服务器配置：获取全量机器配置信息
        内存：系统内存、动态内存、共享内存、session占用内存、内存上下文等占用情况
        集群：通过gs_om、gs_ctl、gsql 三种方式显示集群状态、复制槽、xlog位置等信息
        连接：显示长连接、连接对应的线程占用的资源等情况
        库  ：显示有关数据库大小、权限、表等信息
        用户：显示数据库所有用户、权限等信息
        表  ：显示某数据库中所有表信息、指定表的所有统计信息
        SQL ：显示慢SQL记录情况、排查慢SQL耗时过程、软硬解析情况
        诊断报告：获取指定时间内的诊断报告
    "
    fi

    if [[ "$1" = "help" || "$1" = "server" ]]; then
        echo -e "\t获取服务器配置情况
        format: ogd server [hardware|software]
        eg: ogd server hardware
        [hardware]:输出最关注的服务器硬件配置信息
        eg: ogd server software
        [software]:输出最关注的系统配置信息
        "
    fi
 
    if [[ "$1" = "help" || "$1" = "mem" ]]; then
        echo -e "\t内存问题诊断
        format: ogd mem [sy|us|leakage]
        eg: ogd mem sy
        [sy]:物理内存占用情况
        eg: ogd mem us
        [us]:数据库内存使用情况:输出当前数据库内存相关配置参数、
        内存子模块儿使用情况、动态内存使用情况、TOP50 索引占用内存情况
            eg: ogd mem us sessionId
            [sessid]:获取某session的详细信息
        eg: ogd mem leakage contextname 检查间隔
        [leakage]:诊断某种上下文内存泄漏问题
        "
    fi
    
    if [[ "$1" = "help" || "$1" = "cluster" ]]; then
        echo -e "\t集群状态问题诊断
        format: ogd cluster [om|ctl|sql|tool]
        eg: ogd cluster om
        [om] : 输出om层面集群状态
        eg: ogd cluster ctl
        [ctl]: 输出ctl层面集群状态
        eg: ogd cluster gsql 
        [gsql]: 输出gsql连接后更详细的集群状态。
        eg: ogd cluster tool
        [tool]: 在数据库宕机的情况下输出当前节点
        "
    fi

    if [[ "$1" = "help" || "$1" = "database" ]]; then
        echo -e "\t数据库问题诊断
        format: ogd database [show|assign|phyexam] [databaseName] [schemaName]
        eg: ogd database show
        [show]: 显示当前环境所有数据库信息
        eg: ogd database assign databaseName schemaName
        [assign]: 显示指定数据库详细信息
        eg: ogd database phyexam databaseName schemaName
        [phyexam]: 对指定数据库做体检
        "
    fi
    
    if [[ "$1" = "help" || "$1" = "user" ]]; then
        echo -e "\t数据库用户问题诊断
        format: ogd user [show]
        eg: ogd user show
        [show]: 显示当前数据库所有user信息
        "
    fi
    
    if [[ "$1" = "help" || "$1" = "table" ]]; then
        echo -e "\t数据库表问题诊断
        format: ogd table [show|assign|phyexam] [databaseName] [schemaName] [tableName] 
        eg: ogd table show databaseName schemaName 
        [show]: 显示当前数据库所有表信息
        eg: ogd table assign databaseName schemaName tableName
        [assign]: 显示指定表的全量信息
        "
    fi
    
    if [[ "$1" = "help" || "$1" = "index" ]]; then
        echo -e "\t数据库索引问题诊断
        format: ogd index [show|assign|phyexam] [databaseName] [schemaName] [indexName]
        eg: ogd index show databaseName schemaName 
        [show]: 显示当前数据库所有索引信息
        eg: ogd index assign databaseName schemaName indexName
        [assign]: 显示指定索引的全量信息
        "
    fi

    if [[ "$1" = "help" || "$1" = "sql" ]]; then
        echo -e "\t慢sql问题诊断
        format: ogd sql [show|count|info|parse] [startTime] [endTime] [topNum]
        startimehe endTime格式：20220413010000 
        eg: ogd sql show
        [show]: 显示当前statement_history视图相关参数配置
        eg: ogd sql count [startTime] [endTime]
        [count]: 显示某段时间记录的总的sql数
        eg: ogd sql info [startTime] [endTime] [topNum]
        [info]: 显示某段时间耗时topNum的sql详细信息
        eg: ogd sql parse [startTime] [endTime]
        [parse]: 显示某段时间慢sql软硬解析的比例
        "
    fi
    
    if [[ "$1" = "help" || "$1" = "session" ]]; then
        echo -e "\t连接问题诊断
        format: ogd session [show|count|persistent] [1hour|1min|1s]
        eg: ogd session show
        [show]: 显示当前pg_stat_activity视图相关配置参数
        eg: ogd session count
        [count]: 显示当前pg_stat_activity视图 总、active、idle、idle_in_transication等连接数量
        eg: ogd session persistent [timeRequire]
        [persistent]: 显示当前超过[timeRequire]的长连接
        eg: ogd session assign [sessionId]
        [assign]:显示某连接的资源占用情况
        "
    fi
    
    if [[ "$1" = "help" || "$1" = "checkup" ]]; then
        echo -e "\t体检
        format: ogd checkup [dynamic/static] [1hour|1min|1s]
        eg: ogd checkup dynamic
        [show]: 动态实时展示体检报告
        eg: ogd checkup static 1hour
        [count]: 输出静态体检报告[1min]
        "
    fi
     
    exit 1  
}

#####################################################################
#函数名   ：工具入口
#功能描述 ：选择诊断项输出某类常见问题全量的诊断信息
#输入     ：cpu、mem、cluster等子项列表
#输出     ：对应输入诊断项详细诊断信息
#返回     ：正常结束返回 0
#####################################################################
function main()
{
    # 传参校验
    if [ $# -lt 1 ]; then
        echo -e "\t传参数量不够"
        menu help
    fi

    if [ "$1" = "help" ]; then
        menu help
    fi

    # 库诊断信息一键获取
    if [ "$1" = "server" ]; then
        if [ $# -eq 1 ]; then
            menu server
        fi
    
        if [ "$2" = "hardware" ]; then
        
            echo -e "--------显示当前环境所有硬件信息--------"
            
            echo -e "--------server Model--------"
            dmidecode | grep "System Information" -A9 | grep -E "Manufacturer|Product|serial" | awk '{NF=3}1'
            
            echo -e "--------CPU Model--------"
            lscpu | grep -E "Architecture|CPU|NUMA"
            cpupower -c 0 frequency-info | grep "hardware limits" | awk '{NF=7}1'
            cpupower -c 0 frequency-info | grep "current CPU frequency" | grep Hz | awk '{NF=5}1'
            
            echo -e "--------memory info--------"
            free -g | grep Mem | awk '{print "memeory total is "$2"GB"}'
            
            echo -e "--------disk Model--------"
            lsscsi -Ht
            df -h
            
            echo -e "--------nic info--------"
            net_list=`(ip a | grep mtu | cut -f 2 -d ":")`
            for nic in $net_list
            do
                activeNic=`(ethtool $nic | grep "Link detected" | awk '{print $3}')`
                if [ "$activeNic" = "yes" ]; then
                    echo "$nic is activity"
                    ethtool -i $nic
                    ethtool $nic | grep Speed | awk '{print "$nic speed is " $2}'
                fi
            done
        fi
        
        if [ "$2" = "software" ]; then
        
            echo -e "--------显示当前环境所有软件信息--------"
            
            echo -e "--------BIOS Version--------"
            dmidecode -t 0 | grep SMBIOS | grep -V Getting | awk '{NF=2}1'
            
            echo -e "--------OS version--------"
            cat /etc/*-release
            
            echo -e "--------kernel version--------"
            uname -a
        fi
    
        exit 0
    fi
    
    # 库诊断信息一键获取
    if [ "$1" = "database" ]; then
        if [ $# -eq 1 ]; then
            menu database
        fi
    
        if [ "$2" = "show" ]; then
        
            echo -e "--------显示当前环境所有数据库信息--------"
            ogdEcho "
            select * from pg_database; 
            select n.nspname AS \"Name\",
            pg_catalog.pg_get_userbyid(n.nspowner) AS \"Owner\"
            from pg_catalog.pg_namespace n
            where n.nspname !~ '^pg_' and n.nspname <> 'information_schema'
            order by 1; (\dn)"
            gsql postgres -p $PORT << !
            SELECT n.nspname AS "Name",
            pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
            FROM pg_catalog.pg_namespace n
            WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
            ORDER BY 1;
!
            ogdEcho "
            select pg_database.datname, 
            pg_size_pretty(pg_database_size(pg_database.datname)) as size 
            from pg_database;"
            gsql postgres -p $PORT << !
            select pg_database.datname, 
            pg_size_pretty(pg_database_size(pg_database.datname)) as size 
            from pg_database;
!
            ogdEcho "
            select datname,
            (pg_stat_file(format('%s/%s/PG_VERSION',case when spcname='pg_default' then 'base' else 'pg_tblspc/'||t2.oid||'/PG_11_201804061/' end, t1.oid))).* 
            from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid;"
            gsql postgres -p $PORT << !
            select datname,
            (pg_stat_file(format('%s/%s/PG_VERSION',case when spcname='pg_default' then 'base' else 'pg_tblspc/'||t2.oid||'/PG_11_201804061/' end, t1.oid))).* 
            from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid;
!
        fi
    
        if [ "$2" = "assign" ]; then
        
            echo -e "--------显示指定数据库详细信息--------"
            ogdEcho "
            \c $3;
            set search_path to $4;
            SELECT n.nspname as \"Schema\",
            c.relname as \"Name\",
            CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view'  WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
            pg_catalog.pg_get_userbyid(c.relowner) as \"Owner\",
            pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"Size\",
            c.reloptions as \"Storage\",
            pg_catalog.obj_description(c.oid, 'pg_class') as \"Description\"
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r','v','m','S','f','e','o','')
            AND n.nspname <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND n.nspname !~ '^pg_toast'
            AND c.relname not like 'matviewmap_%'
            AND c.relname not like 'mlog_%'
            AND pg_catalog.pg_table_is_visible(c.oid)
            ORDER BY 1,2;(\d+)"
            
            gsql postgres -p $PORT << ! 
            \c $3;
            set search_path to $4;
            SELECT n.nspname as "Schema",
            c.relname as "Name",
            CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view'  WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
            pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
            pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
            c.reloptions as "Storage",
            pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r','v','m','S','f','e','o','')
            AND n.nspname <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND n.nspname !~ '^pg_toast'
            AND c.relname not like 'matviewmap_%'
            AND c.relname not like 'mlog_%'
            AND pg_catalog.pg_table_is_visible(c.oid)
            ORDER BY 1,2;
!
            ogdEcho "select * from pg_database where datname = '$3';"
            gsql postgres -p $PORT << ! 
            select * from pg_database where datname = '$3';
!
            ogdEcho "select * from pg_stat_database where datname = '$3';"
            gsql postgres -p $PORT << ! 
            select * from pg_stat_database where datname = '$3';
!
        fi
        
        exit 0
    fi

    # 表诊断信息一键获取
    if [ "$1" = "table" ]; then
        if [ $# -eq 1 ]; then
            menu table
        fi
    
        if [ "$2" = "show" ]; then
        
            echo -e "--------显示指定数据库的全量表信息--------"
            ogdEcho "
            \c $3
            set search_path to $4;
            SELECT n.nspname as \"Schema\",
            c.relname as \"Name\",
            CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view'  WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
            pg_catalog.pg_get_userbyid(c.relowner) as \"Owner\",
            pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"Size\",
            c.reloptions as \"Storage\",
            pg_catalog.obj_description(c.oid, 'pg_class') as \"Description\"
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r','v','m','S','f','e','o','')
            AND n.nspname <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND n.nspname !~ '^pg_toast'
            AND c.relname not like 'matviewmap_%'
            AND c.relname not like 'mlog_%'
            AND pg_catalog.pg_table_is_visible(c.oid)
            ORDER BY 1,2;(\d+)"
            gsql postgres -p $PORT << !
            \c $3
            set search_path to $4;
            SELECT n.nspname as "Schema",
            c.relname as "Name",
            CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view'  WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
            pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
            pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
            c.reloptions as "Storage",
            pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relkind IN ('r','v','m','S','f','e','o','')
            AND n.nspname <> 'pg_catalog'
            AND n.nspname <> 'information_schema'
            AND n.nspname !~ '^pg_toast'
            AND c.relname not like 'matviewmap_%'
            AND c.relname not like 'mlog_%'
            AND pg_catalog.pg_table_is_visible(c.oid)
            ORDER BY 1,2;
!
            ogdEcho "
            某数据库下全量表统计信息
            \c $3
            set search_path to $4;
            select relid, relname, n_live_tup, n_dead_tup, last_vacuum, 
            last_autovacuum, last_analyze, last_autoanalyze, last_data_changed 
            from pg_stat_user_tables;"
            gsql postgres -p $PORT << !
            \c $3
            set search_path to $4;
            select relid, relname, n_live_tup, n_dead_tup, last_vacuum, 
            last_autovacuum, last_analyze, last_autoanalyze, last_data_changed 
            from pg_stat_user_tables;
!
            ogdEcho "某数据库下全量表分区表信息
            \c $3
            set search_path to $4;
            select oid,relname,relfilenode, get_table_count(relname::character varying), 
            pg_size_pretty(pg_relation_size(relname::character varying)),
            pg_relation_filepath(relname::character varying) from pg_class where parttype = 'p';"
            gsql postgres -p $PORT << !
            \c $3
            set search_path to $4;
            create or replace function get_table_count(relname character varying)
            returns  integer
            language plpgsql
            AS
            \$\$
            declare
            count integer;
            queryStr text;
            begin
            queryStr:='select count(*) from '||relname;
            EXECUTE queryStr into count;
            return count;
            end;
            \$\$;
            select oid,relname,relfilenode, get_table_count(relname::character varying), 
            pg_size_pretty(pg_relation_size(relname::character varying)),
            pg_relation_filepath(relname::character varying) from pg_class where parttype = 'p';
!
        fi
        
        if [ "$2" = "assign" ]; then
        
            echo -e "--------显示指定数据库指定表信息--------"
            ogdEcho "
            \c $3
            set search_path to $4;
            select a.oid, a.relname, a.relnamespace, a.relfilenode, 
            a.reltablespace, a.parttype, b.schemaname, b.tablespace, 
            b.created, b.last_ddl_time from pg_class a, pg_tables b 
            where a.relname = '$5' and b.tablename = '$5';"
            gsql postgres -p $PORT << !
            \c $3
            set search_path to $4;
            select a.oid, a.relname, a.relnamespace, a.relfilenode, 
            a.reltablespace, a.parttype, b.schemaname, b.tablespace, 
            b.created, b.last_ddl_time 
            from pg_class a, pg_tables b 
            where a.relname = '$5' and b.tablename = '$5';
!
            ogdEcho "
            select pg_relation_filepath('$5');"
            gsql postgres -p $PORT << !
            \c $3
            set search_path to $4;
            select pg_relation_filepath('$5');
!
            ogdEcho "显示指定表的建表语句、建索引语句、autovac状态
            tmp_oid = gsql $3 -p $PORT -c \"select oid from pg_class 
            where relname = '$5';\" | grep -v 'oid\|row\|-' | grep '[0-9]'\"
            tmp_indexrelid=$(gsql $3 -p $PORT -c "select b.indexrelid from pg_class a, pg_index b 
            where a.oid = b.indrelid and a.relname = '$5';" | grep -v 'indexrelid\|rows\|-')
            select pg_get_tabledef($tmp_oid);
            select pg_autovac_status($tmp_oid);"
            tmp_oid=$(gsql $3 -p $PORT -c "select oid from pg_class where relname = '$5';" | grep -v 'oid\|row\|-' | grep '[0-9]')
            echo $tmp_oid
            for i in ${tmp_oid[*]}
            do
                gsql $3 -p $PORT -c "select pg_get_tabledef($i);"
            done
            
            gsql postgres -p $PORT << !
            \c $3
            set search_path to $4;
            select pg_autovac_status($tmp_oid);
!
            ogdEcho "
            \c $3
            set search_path to $4;
            \d+ $5"     
            gsql postgres -p $PORT << !
            \c $3
            set search_path to $4;
            \d+ $5;
!
            ogdEcho "
            \c $3
            set search_path to $4;
            select pg_size_pretty(pg_relation_size('$5')) as size;
            "
            gsql postgres -p $PORT << !
            \c $3
            set search_path to $4;
            select pg_size_pretty(pg_relation_size('$5')) as size;
!
        fi

        exit 0
    fi

    # 索引诊断信息一键获取
    if [ "$1" = "index" ]; then
        if [ $# -eq 1 ]; then
            menu index
        fi
        
        if [ "$2" = "show" ]; then
        
            echo -e "--------显示指定数据库的全量索引信息--------"
            ogdEcho "
            \c $3
            set search_path to $4;
            select indexrelname, pg_size_pretty(pg_relation_size(relid)) 
            from pg_stat_user_indexes 
            order by pg_relation_size(relid) desc; 
            "
            gsql postgres -p $PORT << !
            \c $3
            set search_path to $4;
            select indexrelname, pg_size_pretty(pg_relation_size(relid)) 
            from pg_stat_user_indexes 
            order by pg_relation_size(relid) desc;
!
        fi
        
        if [ "$2" = "assign" ]; then
        
            echo -e "--------显示指定数据库指定索引信息--------"
            ogdEcho "
            \c $3
            set search_path to $4;
            SELECT n.nspname as \"Schema\",
            c.relname as \"Name\",
            CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view'  WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
            pg_catalog.pg_get_userbyid(c.relowner) as \"Owner\",
            c2.relname as \"Table\",
            pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as \"Size\",
            c.reloptions as \"Storage\",
            pg_catalog.obj_description(c.oid, 'pg_class') as \"Description\"
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
            LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
            WHERE c.relkind IN ('i','I','s','')
            AND n.nspname !~ '^pg_toast'
            AND c.relname not like 'matviewmap_%'
            AND c.relname not like 'mlog_%'
            AND c.relname ~ '^($5)$'
            AND pg_catalog.pg_table_is_visible(c.oid)
            ORDER BY 1,2;(\di+)"
            
            gsql postgres -p $PORT << !
            \c $3
            set search_path to $4;
            SELECT n.nspname as "Schema",
            c.relname as "Name",
            CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'I' THEN 'global partition index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'm' THEN 'materialized view'  WHEN 'e' THEN 'stream' WHEN 'o' THEN 'contview' END as "Type",
            pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
            c2.relname as "Table",
            pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
            c.reloptions as "Storage",
            pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
            FROM pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
            LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
            WHERE c.relkind IN ('i','I','s','')
            AND n.nspname !~ '^pg_toast'
            AND c.relname not like 'matviewmap_%'
            AND c.relname not like 'mlog_%'
            AND c.relname ~ '^($5)$'
            AND pg_catalog.pg_table_is_visible(c.oid)
            ORDER BY 1,2;
!
            ogdEcho "
            \c $3
            set search_path to $4;
            select pg_relation_filepath('$5');"
            gsql postgres -p $PORT << !
            \c $3
            set search_path to $4;
            select pg_relation_filepath('$5');
!
            ogdEcho "
            \c $3
            set search_path to $4;
            select pg_size_pretty(pg_relation_size('$5')) as size;"
            gsql postgres -p $PORT << !
            \c $3
            set search_path to $4;
            select pg_size_pretty(pg_relation_size('$5')) as size;
!
        fi

        exit 0
    fi

    # user诊断信息一键获取
    if [ "$1" = "user" ]; then
        if [ $# -eq 1 ]; then
            menu user
        fi
        
        if [ "$2" = "show" ]; then
        
            echo -e "\t显示当前环境所有用户信息"
            ogdEcho "
            SELECT r.rolname, r.rolsuper, r.rolinherit,
            r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
            r.rolconnlimit, r.rolvalidbegin, r.rolvaliduntil,
            ARRAY(SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
            WHERE m.member = r.oid) as memberof
            , r.rolreplication
            , r.rolauditadmin
            , r.rolsystemadmin
            , r.rolmonitoradmin
            , r.roloperatoradmin
            , r.rolpolicyadmin
            , r.roluseft
            , r.rolkind
            FROM pg_catalog.pg_roles r
            ORDER BY 1; \du"
            gsql postgres -p $PORT << !
            SELECT r.rolname, r.rolsuper, r.rolinherit,
            r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
            r.rolconnlimit, r.rolvalidbegin, r.rolvaliduntil,
            ARRAY(SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
            WHERE m.member = r.oid) as memberof
            , r.rolreplication
            , r.rolauditadmin
            , r.rolsystemadmin
            , r.rolmonitoradmin
            , r.roloperatoradmin
            , r.rolpolicyadmin
            , r.roluseft
            , r.rolkind
            FROM pg_catalog.pg_roles r
            ORDER BY 1;
!
            ogdEcho "
            select * from pg_user;"
            gsql postgres -p $PORT << !
            select * from pg_user;
!
        fi
        
        exit 0
    fi

    # 连接诊断信息一键获取
    if [ "$1" = "session" ]; then
        if [ $# -eq 1 ]; then
            menu session
        fi
        
        if [ "$2" = "show" ]; then
        
            echo -e "--------显示pg_stat_activity视图依赖的参数--------"
            gsql postgres -p $PORT << !
            show track_counts;
!
        fi
        
        if [ "$2" = "count" ]; then
            
            echo -e "--------显示当前连接数(总、活跃、idle、其他)--------"
            ogdEcho "
            select count(distince state), state from pg_stat_activity group by state;"
            gsql postgres -p $PORT << ! 
            select state,count(*) state from pg_stat_activity group by state;
!
        fi
        
        if [ "$2" = "persistent" ]; then
            
            echo -e "--------显示当前超过一定耗时的长连接--------"
            ogdEcho "
            select * from pg_stat_activity where current_timestamp - query_start > interval '$3';"
            gsql postgres -p $PORT << ! 
            select * from pg_stat_activity where current_timestamp - query_start > interval '$3';
!
        fi
        
        if [ "$2" = "assign" ]; then
            
            echo -e "--------显示某连接的资源占用情况--------"
            tmp_lwtid=$(gsql postgres -p $PORT -c "select lwtid from pg_thread_wait_status where tid = '$3';" | grep '[0-9]' | grep -v 'row\|--')
            tmp_pid=$(ps ux | grep gaussdb | grep -v grep | awk '{print $2}')
            
            echo "pid:$tmp_pid  lwtid:$tmp_lwtid"
            top -p $tmp_pid -H -n 1 | grep $tmp_lwtid | awk '{print "CPU""\t""MEM"} {print $10"\t"$11}'
        fi
        
        exit 0
    fi

    # 慢sql诊断信息一键获取
    if [ "$1" = "sql" ]; then
        if [ $# -eq 1 ]; then
            menu sql
        fi
        
        stime=$3
        etime=$4
        
        if [ "$2" = "show" ]; then
        
            echo -e "\t显示statement_history视图依赖的参数"
            gsql postgres -p $PORT << !
            show enable_stmt_track;
            show track_stmt_stat_level;
            show track_stmt_session_slot;
            show track_stmt_details_size;
            show track_stmt_retention_time;
            show instr_unique_sql_count;
            show log_min_duration_statement;
!
        fi
        
        if [ "$2" = "count" ]; then
            
            echo -e "--------显示某段时间记录的总的sql数--------"
            ogdEcho "
            select count(*) from dbe_perf.statement_history 
            where start_time > to_date('$stime','yyyymmddhh24miss')
            and start_time < to_date('$etime','yyyymmddhh24miss');"
            gsql postgres -p $PORT << ! 
            select count(*) from dbe_perf.statement_history 
            where start_time > to_date('$stime','yyyymmddhh24miss')
            and start_time < to_date('$etime','yyyymmddhh24miss');
!
        fi
        
        if [ "$2" = "info" ]; then
        
            echo -e "--------显示某段时间耗时TOP $5长的sql详细信息--------"
            ogdEcho "
            select * from dbe_perf.statement_history 
            where start_time > to_date('$stime','yyyymmddhh24miss')
            and start_time < to_date('$etime','yyyymmddhh24miss')
            order by db_time desc limit $5;"
            gsql postgres -p $PORT << ! 
            select * from dbe_perf.statement_history 
            where start_time > to_date('$stime','yyyymmddhh24miss')
            and start_time < to_date('$etime','yyyymmddhh24miss')
            order by db_time desc limit $5;
!
        fi
        
        if [ "$2" = "parse" ]; then
        
            echo -e "--------显示某段时间慢sql软硬解析的比例--------"
            gsql postgres -p $PORT << ! 
            select application_name,left(query,20), n_soft_parse, n_hard_parse,
            sum(db_time) db_time, sum(cpu_time) cpu_time, sum(execution_time) exec_time,
            sum(parse_time) parse_time,sum(plan_time) plan_time, sum(data_io_time) data_io_time,
            sum(lock_time) lock_time, count(*) count
            from dbe_perf.statement_history 
            where start_time > to_date('$stime','yyyymmddhh24miss')
            and start_time < to_date('$etime','yyyymmddhh24miss')
            group by application_name, query, n_soft_parse,n_hard_parse
            order by 5 desc;
!
            echo -e "--------获取query语句执行计划软硬解析的情况--------"
            gsql postgres -p $PORT << ! 
            select left(query,30), n_soft_parse, n_hard_parse, count(*),
            sum(db_time) db_time, sum(plan_time) plan_time, 
            decode(sum(db_time),0,0, sum(plan_time)/sum(db_time)) plan_ratio
            from dbe_perf.statement_history 
            where application_name='PostgreSQL JDBC Driver'
            group by query, n_soft_parse, n_hard_parse
            order by 7 desc;
!
        fi
        
        exit 0
    fi

    # 集群状态相关诊断信息一键获取
    if [ "$1" = "cluster" ]; then
        if [ $# -eq 1 ]; then
            menu cluster
        fi
        
        if [ "$2" = "om" ]; then
            
            echo -e "--------数据库gs_om可以获取到的所有集群信息--------"
            ogdEcho " gs_om -t status --detail"
            gs_om -t status --detail
            gs_om -V
        fi
        
        if [ "$2" = "ctl" ]; then
            
            echo -e "--------数据库gs_ctl可以获取到的所有集群信息--------"
            ogdEcho "gs_ctl query -D $GAUSSDATA"
            gs_ctl query -D $GAUSSDATA
        fi
        
        if [ "$2" = "gsql" ]; then
            
            echo -e "--------数据库gsql连接后查询到的集群详细信息--------"
            echo -e "--------数据库日志同步状态信息，可查看发起段发送日志位置，接收端接收日志位置信息--------"
            ogdEcho "select * from pg_stat_replication"
            gsql postgres -p $PORT << ! 
            select * from pg_stat_replication;
!
            echo -e "--------查看各个复制节点的信息(物理复制槽/逻辑复制槽、槽位是否为激活状态、是否为假备)--------"
            ogdEcho "select * from pg_replication_slots;"
            gsql postgres -p $PORT << ! 
            select * from pg_replication_slots;
!
            echo -e "--------获取当前事务日志的写入位置--------"
            ogdEcho "select pg_current_xlog_location();"
            gsql postgres -p $PORT << ! 
            select pg_current_xlog_location();
!
            echo -e "--------获取当前事务日志的写入位置，并转换成对应的文件命名格式--------"
            ogdEcho "select * from pg_xlogfile_name(pg_current_xlog_location());"
            gsql postgres -p $PORT << !
            select * from pg_xlogfile_name(pg_current_xlog_location());
!
        fi
        
        if [ "$2" = "tool" ]; then
            echo -e "--------数据库pg_controldata工具查看checkpoint位点--------"
            ogdEcho "\tpg_controldata $GAUSSDATA"
            pg_controldata $GAUSSDATA
        fi
        
        exit 0
    fi

    # 操作系统内存和数据库内存诊断信息一键获取
    if [ "$1" = "mem" ]; then
        if [ $# -eq 1 ]; then
            menu mem
        fi
        
        if [ "$2" = "sy" ]; then
            ogdEcho "free -g"
            free -g
            ogdEcho "ipcs -m"
            ipcs -m
        fi
        
        if [ "$2" = "us" ]; then
            echo -e "--------内存相关所有参数--------"
            gsql postgres -p $PORT << !
            show max_process_memory;
            show shared_buffers;
!
            echo -e "--------数据库内存详细信息(查看当前数据库配置内各子模块使用情况)--------"
            ogdEcho "select * from pg_total_memory_detail;"
            gsql postgres -p $PORT << !
            select * from pg_total_memory_detail;
!
            echo -e "--------数据库session层面内存详细信息--------"
            ogdEcho "
            select contextname, 
            sum(totalsize)/1024/1024 sum,
            sum(freesize)/1024/1024, 
            count(*) count
            from gs_session_memory_detail
            group by contextname order by sum desc limit 10;"
            gsql postgres -p $PORT << !
            select contextname, 
            sum(totalsize)/1024/1024 sum,
            sum(freesize)/1024/1024, 
            count(*) count
            from gs_session_memory_detail
            group by contextname order by sum desc limit 10;
!
            echo -e "--------数据库动共享内存使用详细信息(使用内存最大的context可能存在内存泄漏)--------"
            ogdEcho "
            select contextname, 
            sum(totalsize)/1024/1024 sum,
            sum(freesize)/1024/1024, 
            count(*) count
            from gs_shared_memory_detail
            group by contextname order by sum desc limit 10;"
            gsql postgres -p $PORT << !
            select contextname, 
            sum(totalsize)/1024/1024 sum,
            sum(freesize)/1024/1024, 
            count(*) count
            from gs_shared_memory_detail
            group by contextname order by sum desc limit 10;
!
            echo -e "--------数据库所有session内存占用大小划分--------"
            ogdEcho "
            create temp table memoryInfo as
            select total, cache from
            (select sessid as sessid, sum(totalsize)/1024/1024 as total
            from gs_session_memory_detail
            where sesstype='postgres'
            group by sessid) as a,
            (select sessid, totalsize/1024/1024 as cache
            from gs_session_memory_detail
            where contextname='SessionCacheMemoryContext') as b
            where a.sessid=b.sessid
            order by total desc;
            select '0-10' as range, sum(total) as total, sum(cache) as cache, sum(cache)/sum(total) as ratio, count(*) as count
            from memoryInfo where total>=0 and total<10;
            select '10-20' as range, sum(total) as total, sum(cache) as cache, sum(cache)/sum(total) as ratio, count(*) as count
            from memoryInfo where total>=10 and total<20;
            select '20-30' as range, sum(total) as total, sum(cache) as cache, sum(cache)/sum(total) as ratio, count(*) as count
            from memoryInfo where total>=20 and total<30;
            select '30-40' as range, sum(total) as total, sum(cache) as cache, sum(cache)/sum(total) as ratio, count(*) as count
            from memoryInfo where total>=30 and total<40;
            select '40-50' as range, sum(total) as total, sum(cache) as cache, sum(cache)/sum(total) as ratio, count(*) as count
            from memoryInfo where total>=40 and total<50;
            select '50-max' as range, sum(total) as total, sum(cache) as cache, sum(cache)/sum(total) as ratio, count(*) as count
            from memoryInfo where total>=50;"
               
            gsql postgres -p $PORT << !
            create temp table memoryInfo as
            select total, cache from
            (select sessid as sessid, sum(totalsize)/1024/1024 as total
            from gs_session_memory_detail
            where sesstype='postgres'
            group by sessid) as a,
            (select sessid, totalsize/1024/1024 as cache
            from gs_session_memory_detail
            where contextname='SessionCacheMemoryContext') as b
            where a.sessid=b.sessid 
            order by total desc;
            select '0-10' as range, sum(total) as total, sum(cache) as cache, sum(cache)/sum(total) as ratio, count(*) as count
            from memoryInfo where total>=0 and total<10;
            select '10-20' as range, sum(total) as total, sum(cache) as cache, sum(cache)/sum(total) as ratio, count(*) as count
            from memoryInfo where total>=10 and total<20;
            select '20-30' as range, sum(total) as total, sum(cache) as cache, sum(cache)/sum(total) as ratio, count(*) as count
            from memoryInfo where total>=20 and total<30;
            select '30-40' as range, sum(total) as total, sum(cache) as cache, sum(cache)/sum(total) as ratio, count(*) as count
            from memoryInfo where total>=30 and total<40;
            select '40-50' as range, sum(total) as total, sum(cache) as cache, sum(cache)/sum(total) as ratio, count(*) as count
            from memoryInfo where total>=40 and total<50;
            select '50-max' as range, sum(total) as total, sum(cache) as cache, sum(cache)/sum(total) as ratio, count(*) as count
            from memoryInfo where total>=50;    
!
            echo -e "--------数据库索引占用内存大小--------"
            ogdEcho "
            select * from gs_session_memory_detail 
            where contextname like '%index%' or contextname like '%idx%'
            order by totalsize desc limit 50;"
            gsql postgres -p $PORT << !
            select * from gs_session_memory_detail 
            where contextname like '%index%' or contextname like '%idx%'
            order by totalsize desc limit 50;
!
            echo -e "--------数据库某session占用总的总内存信息--------"                         
            if [ "$3" != "" ]; then
                echo -e "\t数据库某session占用总内存"
                ogdEcho "
                select sum(totalsize)/1024/1024 from gs_session_memory_detail 
                where sessid like '%$3%';"
                gsql postgres -p $PORT << !
                select sum(totalsize)/1024/1024 from gs_session_memory_detail 
                where sessid like '%$3%';
!
                echo -e "--------数据库某session内占用内存TOP的session--------"
                ogdEcho "
                select * from gs_session_memory_detail
                where sessid like '%$3%'
                order by totalsize desc limit 10;"
                gsql postgres -p $PORT << !
                select * from gs_session_memory_detail
                where sessid like '%$3%'
                order by totalsize desc limit 10;
!
            fi
        fi
        
        if [ "$2" = "leakage" ]; then
            echo -e "--------跟踪$3内存上下文信息持续跟踪$4个周期--------"
            gsql postgres -p $PORT << !
            select dbe_perf.track_memory_context('$3');
            select * from dbe_perf.track_memory_context_detail();
            select pg_sleep($4);
            select * from dbe_perf.track_memory_context_detail();
            select dbe_perf.track_memory_context(' ');
!
        fi
        
        exit 0
    fi
    
    # 生成体检报告
    if [ "$1" = "checkup" ]; then
        if [ $# -eq 1 ]; then
            menu checkup
        fi
        
        if [ "$2" = "dynamic" ]; then
            for i in $(seq 1 $3)
            do
                    ogdCheckupInput
                    clear
                    ogdCheckupOutput
                    sleep 5
            done
        
        fi

        if [ "$2" = "static" ]; then
            
            echo -e "--------输出一段时间离线体检报告--------"
        fi
        
        exit 0
    fi
}

####################################################################
#函数名    ：echoMode
#功能描述  ：实现输出信息的debug模式和release模式的切换
#输入      ：mode=[debug|release]默认release，需要输出的内容
#输出      ：release下输出空，debug模式下输出传入的内容
#返回      ：执行结束返回 0
####################################################################
function ogdEcho()
{
    if [ "$mode" = "debug" ]; then
        echo -e "$1"
    fi

    if [ "$mode" = "release" ]; then
        echo "">/dev/null
    fi
}

####################################################################
#函数名    ：ogdCheckupInput
#功能描述  ：获取体检信息，并保存到运维表
#输入      ：无
#输出      ：无
#返回      ：执行结束返回 0
####################################################################
function ogdCheckupInput()
{
    current=`date '+%Y-%m-%d %H:%M:%S'`

    # 系统CPU
    ogdEcho "--------CPU--------"
    top -n 1 | grep "%Cpu(s)" | awk '{print int($2), int($4)}' | while read a b
    do
        sum=`expr $a + $b`
        gsql -d postgres -p $PORT -c "insert into monitor_os_cpu values($sum,$a,$b,'$current')" >/dev/null
    done

    # 系统内存
    ogdEcho "--------Mem--------"
    top -n 1 | grep "MiB Mem" | awk '{print int($4/1024), int($6/1024), int($8/1024)}' | while read a b c
    do
        gsql -d postgres -p $PORT -c "insert into monitor_os_mem values($a,$b,$c,'$current')" >/dev/null
    done

    # swap
    ogdEcho "--------Swap--------"
    top -n 1 | grep "MiB Swap" | awk '{print int($3/1024), int($5/1024), int($7/1024)}' | while read a b c
    do
        gsql -d postgres -p $PORT -c "insert into monitor_os_swap values($a,$b,$c,'$current')"  >/dev/null
    done

    # 磁盘io
    ogdEcho "--------IO--------"
    i=1
    iostat -xm | grep "sda\|sdb" | awk '{print $3,$9,$21}' | while read a b c
    do
        case $i in
            1)
                gsql -d postgres -p $PORT -c "insert into monitor_os_io values($a,$b,$c,0,0,0,0,'$current')" >/dev/null
            ;;
            2)
                gsql -d postgres -p $PORT -c "update monitor_os_io set os_ogxlog_r = $a, os_ogxlog_w = $b, os_ogxlog_u = $c, os_io_state = 1 where os_io_state = 0" >/dev/null
            ;;
            *)
                echo "error"
        esac
        i=`expr $i + 1`
    done

    # 网络
    ogdEcho "--------Net--------"
    sar -n DEV | grep "Average:    $nic" | awk '{print $3,$4,$5,$6}' | while read a b c d
    do
        gsql -d postgres -p $PORT -c "insert into monitor_os_net values($a,$b,$c,$d,'$current')" >/dev/null
    done

    # 数据库容量
    ogdEcho "--------Cap--------"
    i=1
    gsql -d postgres -p $PORT -c "select datname,pg_database_size(pg_database.datname)/1024/1024 as size from pg_database order by size desc limit 5;" | grep -v "size\|row\|--" | grep [A-Za-a0-9] | awk '{print $1,$3}' | while read a b
    do
        case $i in
           1)
                gsql -d postgres -p $PORT -c "insert into monitor_db_cap values('$a',$b,0,0,0,0,0,0,0,0,0,'$current')" >/dev/null
                ;;
           2)
                gsql -d postgres -p $PORT -c "update monitor_db_cap set db_cap_top2_db_name = '$a', db_cap_top2_db_size = $b where db_cap_state = 0" >/dev/null
                ;;
           3)
                gsql -d postgres -p $PORT -c "update monitor_db_cap set db_cap_top3_db_name = '$a', db_cap_top3_db_size = $b where db_cap_state = 0" >/dev/null
                ;;
           4)
                gsql -d postgres -p $PORT -c "update monitor_db_cap set db_cap_top4_db_name = '$a', db_cap_top4_db_size = $b where db_cap_state = 0" >/dev/null
                ;;
           5)
                gsql -d postgres -p $PORT -c "update monitor_db_cap set db_cap_top5_db_name = '$a', db_cap_top5_db_size = $b, db_cap_state = 1 where db_cap_state = 0" >/dev/null
                ;;
           *)
                echo "error"
        esac
        i=`expr $i + 1`
    done

    # 连接数
    ogdEcho "--------session--------"
    i=1
    gsql -d postgres -p $PORT -c "select (select count(*) from pg_stat_activity),state,count(*) count from pg_stat_activity group by state;"  | grep -v "count\|--\|row" | grep [A-Za-z0-9] | awk '{print $1,$5}' | while read a b
    do
        case $i in
            1)
                gsql -d postgres -p $PORT -c "insert into monitor_db_session values($a,$b,0,0,'$current')" >/dev/null
                ;;
            2)
                gsql -d postgres -p $PORT -c "update monitor_db_session set db_session_idle = $b, db_session_state = 1 where db_session_state = 0" >/dev/null
                ;;
            *)
                echo "error"
        esac
        i=`expr $i + 1`
    done

    # 表
    ogdEcho "--------table--------"
    OLD_IFS="$IFS"
    IFS="|"

    dft="2020-06-30 00:00:00.000001"
    i=1
    gsql -d postgres -p $PORT -c "select relname, last_analyze, last_vacuum, n_dead_tup, last_data_changed, schemaname from pg_stat_user_tables order by last_data_changed limit 5;" | grep -v "count\|--\|row" | grep [A-Za-z0-9] | grep [0-9] | while read a b c d e f
    do
        a=`echo $a | awk '{print $1}'`
        b=`echo $b | awk '{print $1,$2}'`
        c=`echo $c | awk '{print $1,$2}'`
        d=`echo $d | awk '{print $1}'`
        e=`echo $e | awk '{print $1,$2}'`
        f=`echo $f | awk '{print $1}'`

        cap=`gsql -d postgres -p $PORT -c "select pg_relation_size('$f.$a');"  | grep -v "pg_relation_size\|row\|--" | grep [0-9] | awk '{print $1/1024/1024}'`

        ogdEcho $a"--"$cap"--"$b"--"$c"--"$d"--"$e"--"$f


        if  echo $b | grep -q ^[0-9] || [ `echo $b |grep  "+"` ] || [ `echo $b |grep  "="` ] || [ `echo $b |grep  ":"` ] ;then
            echo "OK" >/dev/null
        else
            b=$dft
        fi

        if  echo $c | grep -q ^[0-9] || [ `echo $c |grep  "+"` ] || [ `echo $c |grep  "="` ] || [ `echo $c |grep  ":"` ] ;then
            echo "OK" >/dev/null
        else
            c=$dft
        fi

        if  echo $e | grep -q ^[0-9] || [ `echo $e |grep  "+"` ] || [ `echo $e |grep  "="` ] || [ `echo $e |grep  ":"` ] ;then
            echo "OK" >/dev/null
        else
            e=$dft
        fi

        ogdEcho $a"++"$cap"++"$b"++"$c"++"$d"++"$e"++"$f

        case $i in
            1)
                gsql -d postgres -p $PORT -c "
                    insert into monitor_db_table values(
                    '$a',$cap,'$b','$c',$d,'$e',
                    '0',0,'$dft','$dft',0,'$dft',
                    '0',0,'$dft','$dft',0,'$dft',
                    '0',0,'$dft','$dft',0,'$dft',
                    '0',0,'$dft','$dft',0,'$dft',
                    0,'$current')" >/dev/null
                 ;;
            2)
                 gsql -d postgres -p $PORT -c "
                    update monitor_db_table 
                    set db_table2_name= '$a', db_table2_cap = $cap, 
                    db_table2_lastanalyze = '$b',db_table2_lastvacuum = '$c', 
                    db_table2_deadtuple = $d, db_table2_lastdatachanged = '$e'
                    where db_table_state = 0" >/dev/null
                 ;;
            3)
                 gsql -d postgres -p $PORT -c "
                    update monitor_db_table 
                    set db_table3_name= '$a', db_table3_cap = $cap, 
                    db_table3_lastanalyze = '$b',db_table3_lastvacuum = '$c', 
                    db_table3_deadtuple = $d, db_table3_lastdatachanged = '$e'
                    where db_table_state = 0" >/dev/null
                 ;;
            4)
                 gsql -d postgres -p $PORT -c "
                    update monitor_db_table 
                    set db_table4_name= '$a', db_table4_cap = $cap, 
                    db_table4_lastanalyze = '$b', db_table4_lastvacuum = '$c', 
                    db_table4_deadtuple = $d, db_table4_lastdatachanged = '$e' 
                    where db_table_state = 0" >/dev/null
                 ;;
            5)
                 gsql -d postgres -p $PORT -c "
                    update monitor_db_table 
                    set db_table5_name= '$a', db_table5_cap = $cap, 
                    db_table5_lastanalyze = '$b',db_table5_lastvacuum = '$c', 
                    db_table5_deadtuple = $d, db_table5_lastdatachanged = '$e', 
                    db_table_state = 1 where db_table_state = 0" >/dev/null
                  ;;
            *)
                 echo "error"
        esac
        i=`expr $i + 1`
    done

    IFS=$OLD_IFS

    #索引
    ogdEcho "--------Index--------"
    i=1
    gsql -d postgres -p $PORT -c "select indexrelname, pg_relation_size(relid)/1024/1024 as size_mb from pg_stat_user_indexes order by pg_relation_size(relid) desc limit 5;" | grep -v "rows\|+\| size_mb" | grep [A-Za-z0-9] | awk '{print $1, $3}' | while read a b
    do
        case $i in
            1)
                gsql -d postgres -p $PORT -c "insert into monitor_db_index values('$a',$b,'0',0,'0',0,'0',0,'0',0,0,'$current')" >/dev/null
                ;;
            2)
                gsql -d postgres -p $PORT -c "update monitor_db_index set db_index2_name = '$a', db_index2_size = $b where db_index_state = 0" >/dev/null
                ;;
            3)
                gsql -d postgres -p $PORT -c "update monitor_db_index set db_index3_name = '$a', db_index3_size = $b where db_index_state = 0" >/dev/null
                ;;

            4)
                gsql -d postgres -p $PORT -c "update monitor_db_index set db_index4_name = '$a', db_index4_size = $b where db_index_state = 0" >/dev/null
                ;;

            5)
                gsql -d postgres -p $PORT -c "update monitor_db_index set db_index5_name = '$a', db_index5_size = $b, db_index_state = 1 where db_index_state = 0" >/dev/null
                ;;

            *)
                echo "error"
        esac
        i=`expr $i + 1`
    done

    # sql
    ogdEcho "--------Sql--------"
    gsql -d postgres -p $PORT -c "
        create temp table slowsql_info as select db_time from dbe_perf.statement_history order by db_time desc;
        select '<20ms' as range, count(*) as count from slowsql_info where db_time >= 0 and db_time < 20000;
        select '20ms-100ms' as range, count(*) as count from slowsql_info where db_time >= 20000 and db_time < 100000;
        select '100ms-500ms' as range, count(*) as count from slowsql_info where db_time >= 100000 and db_time < 500000;
        select '500ms-1000ms' as range, count(*) as count from slowsql_info where db_time >= 500000 and db_time < 1000000;
        select '>1000ms' as range, count(*) as count from slowsql_info where db_time >= 1000000;
        "  | grep -v "count\|--\|row\|INSERT" | grep [0-9] | awk '{print $3}' | xargs | while read a b c d e
    do
        gsql -d postgres -p $PORT -c "insert into monitor_db_sql values($a,$b,$c,$d,$e,'$current')" >/dev/null
    done

    # 日志
    ogdEcho "--------log--------"
    totallogsize=$(du -s `gsql -d postgres -p $PORT -c "show log_directory" | grep -v "log_directory\|--\|row" | grep [A-Za-z0-9]` | awk '{print $1/1024}')
    currentlogsize=$(ls -lt `gsql -d postgres -p $PORT -c "show log_directory" | grep -v "log_directory\|--\|row" | grep [A-Za-z0-9]` | sed -n '2p' | awk '{print $5/1024/1024}')

    gsql -d postgres -p $PORT -c "insert into monitor_db_log values($totallogsize,$currentlogsize,'$current')" >/dev/null

    # xlog
    ogdEcho "--------xlog--------"
    totalxlogsize=$(du -s $XLOGDATA | awk '{print $1/1024}')
    totalxlogcount=$(ls $XLOGDATA | wc -l)
    additionlastmin=$(find $XLOGDATA -name '*' -mmin -1 | wc -l)
    currentxloginsert=`gsql -d postgres -p $PORT -c "select * from pg_current_xlog_location();" | grep -v "pg_current_xlog_location\|row\|--" | grep [A-Za-z0-9] | awk '{print $1}'`
    initlocation=0/00000000

    gsql -d postgres -p $PORT -c "select receiver_replay_location from pg_stat_replication ;" | grep -v         "receiver_replay_location\|row\|--" | grep [A-Za-z0-9] | xargs | while read a b c d e f g h
    do
        ogdEcho $a"--"$b"--"$c"--"$d"--"$e"--"$f"--"$g"--"$h

        if  echo $a | grep -q ^[0-9] || [ `echo $a |grep  "+"` ] || [ `echo $a |grep  "="` ] || [ `echo $a |grep  ":"` ] ;then
            echo "OK" >/dev/null
        else
            a=$initlocation
        fi

        if  echo $b | grep -q ^[0-9] || [ `echo $b |grep  "+"` ] || [ `echo $b |grep  "="` ] || [ `echo $b |grep  ":"` ] ;then
            echo "OK" >/dev/null
        else
            b=$initlocation
        fi
        
        if  echo $c | grep -q ^[0-9] || [ `echo $c |grep  "+"` ] || [ `echo $c |grep  "="` ] || [ `echo $c |grep  ":"` ] ;then
            echo "OK" >/dev/null
        else
            c=$initlocation
        fi
        
        if  echo $d | grep -q ^[0-9] || [ `echo $d |grep  "+"` ] || [ `echo $d |grep  "="` ] || [ `echo $d |grep  ":"` ] ;then
            echo "OK" >/dev/null
        else
            d=$initlocation
        fi    

        if  echo $e | grep -q ^[0-9] || [ `echo $e |grep  "+"` ] || [ `echo $e |grep  "="` ] || [ `echo $e |grep  ":"` ] ;then
            echo "OK" >/dev/null
        else
            e=$initlocation
        fi

        if  echo $f | grep -q ^[0-9] || [ `echo $f |grep  "+"` ] || [ `echo $f |grep  "="` ] || [ `echo $f |grep  ":"` ] ;then
            echo "OK" >/dev/null
        else
            f=$initlocation
        fi                

        if  echo $g | grep -q ^[0-9] || [ `echo $g |grep  "+"` ] || [ `echo $g |grep  "="` ] || [ `echo $g |grep  ":"` ] ;then
            echo "OK" >/dev/null
        else
            g=$initlocation
        fi

        if  echo $h | grep -q ^[0-9] || [ `echo $h |grep  "+"` ] || [ `echo $h |grep  "="` ] || [ `echo $h |grep  ":"` ] ;then
            echo "OK" >/dev/null
        else
            h=$initlocation
        fi

        ogdEcho $a"++"$b"++"$c"++"$d"++"$e"++"$f"++"$g"++"$h

        gsql -d postgres -p $PORT -c "insert into monitor_db_xlog values($totalxlogsize,$totalxlogcount,$additionlastmin,'$currentxloginsert','$a','$b','$c','$d','$e','$f','$g','$h','$current');" >/dev/null
    done

    # 数据库内存
    ogdEcho "--------context--------"
    gsql -d postgres -p $PORT -c "select memorytype,memorymbytes from pg_total_memory_detail;" | grep "max_dynamic_memory\|dynamic_used_memory\|max_shared_memory\|shared_used_memory" | grep -v gpu | awk '{print $3}' | xargs | while read a b c d
    do
        gsql -d postgres -p $PORT -c "
            insert into monitor_db_mem values(
            $a,$b,$c,$d,
            '0',0,
            '0',0,
            '0',0,
            '0',0,
            '0',0,
            0,'$current')" >/dev/null
    done

    i=1
    gsql -d postgres -p $PORT -c "select contextname,sum(totalsize)/1024/1024 sum from gs_shared_memory_detail group by contextname order by sum desc limit 5;" | grep -v "contextname\|row\|--" | grep [A-Za-z0-9] | awk '{print $1, $3}' | while read a b
    do
        case $i in
           1)
                gsql -d postgres -p $PORT -c "update monitor_db_mem set db_mem_context1_name= '$a', db_mem_context1_size = $b where db_mem_state = 0" >/dev/null
                ;;
           2)
                gsql -d postgres -p $PORT -c "update monitor_db_mem set db_mem_context2_name= '$a', db_mem_context2_size = $b where db_mem_state = 0" >/dev/null
                ;;
           3)
                gsql -d postgres -p $PORT -c "update monitor_db_mem set db_mem_context3_name= '$a', db_mem_context3_size = $b where db_mem_state = 0" >/dev/null
                ;;
           4)
                gsql -d postgres -p $PORT -c "update monitor_db_mem set db_mem_context4_name= '$a', db_mem_context4_size = $b where db_mem_state = 0" >/dev/null
                ;;
           5)
                gsql -d postgres -p $PORT -c "update monitor_db_mem set db_mem_context5_name= '$a', db_mem_context5_size = $b, db_mem_state = 1 where db_mem_state = 0" >/dev/null
                ;;
           *)
                echo "error"
        esac
        i=`expr $i + 1`
    done 
    
    return 0
}

####################################################################
#函数名    ：ogdCheckupOutput
#功能描述  ：实时输出体检信息
#输入      ：无
#输出      ：动态实时展示体检报告
#返回      ：执行结束返回 0
####################################################################
function ogdCheckupOutput()
{
    chechupTime=`gsql -d postgres -p 12347 -c "select * from monitor_os_cpu order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print $7,$8}'`

    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    echo -e "|                                                         openGauss体检报告                                  "
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    echo -e "|                                                         $chechupTime                                        "
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select * from monitor_os_cpu order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| 占用总CPU(%):\t" $1 "\t\t| sys占用:(%)\t" $3 "\t\t| us占用(%): \t" $5}'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select * from monitor_os_mem order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| totalMem(MB):\t" $1 "\t\t| free(MB):\t" $3 "\t\t| used(MB): \t" $5}'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select * from monitor_os_swap order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| totalMem(MB):\t" $1 "\t\t| free(MB):\t" $3 "\t\t| used(MB): \t" $5}'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select os_ogdata_r,os_ogdata_w,os_ogdata_u,os_ogxlog_r,os_ogxlog_w,os_ogxlog_u,collect_time from monitor_os_io order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| ogdataRead(MB/s):" $1 "\t\t| ogdataWrite(MB/s):" $3 "\t\t| ogdataUtil(%): " $5 "\t\t\n| ogxlogRead(MB/s): " $7 "\t\t| ogxlogWrite(MB/s):" $9 "\t\t| ogxlog_Util(%): " $11}'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select * from monitor_os_net order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| rxpc(k/s):\t" $1 "\t\t| txpc(k/s):\t" $3 "\t\t| rxk(B/s):\t" $5 "\t\t| txk(B/s):\t" $7}'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select * from monitor_db_cap order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| topSize1(MB):" $3 "\t\t| Database:" $1 "\n| topSize2(MB):" $7 "\t\t| Database:" $5 "\n| topSize3(MB):" $11 "\t\t| Database:" $9 "\n| topSize4(MB):" $15 "\t\t| Database:" $13 "\n| topSize5(MB):" $19 "\t\t| Database:" $17}'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select * from monitor_db_session order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| totalSession:" $1 "\t\t| activeSession:" $3 "\t\t| idleSession:" $5}'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select * from monitor_db_table order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| hotTable1:" $1 "\n| Cap(MB):" $3 "\t\t\t| Lastanalyze:" $5,$6 " | Lastvacuum:" $8,$9 " | deadtuple:" $11 "\t| Lastdatachange:" $13,$14 " \n| hotTable2:" $16 "\n| Cap(MB):" $18 "\t\t\t| Lastanalyze:" $20,$21 " | Lastvacuum:" $23,$24 " | deadtuple:" $26 "\t| Lastdatachange:" $28,$29 " \n| hotTable3:" $31 "\n| Cap(MB):" $33 "\t\t\t| Lastanalyze:" $35,$36 " | Lastvacuum:" $38,$39 " | deadtuple:" $41 "\t| Lastdatachange:" $43,$44 " \n| hotTable4:" $46 "\n| Cap(MB):" $48 "\t\t\t| Lastanalyze:" $50,$51 " | Lastvacuum:" $53,$54 " | deadtuple:" $56 "\t| Lastdatachange:" $58,$59 " \n| hotTable5:" $61 "\n| Cap(MB):" $63 "\t\t\t| Lastanalyze:" $65,$66 " | Lastvacuum:" $68,$69 " | deadtuple:" $71 "\t| Lastdatachange:" $73,$74}'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select * from monitor_db_index order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| topsize1(MB):" $3 "\t\t| index:" $1 "\n| topsize2(MB):" $7 "\t\t| index:" $5 "\n| topsize3(MB):" $11 "\t\t| index:" $9 "\n| topsize4(MB):" $15 "\t\t| index:" $13 "\n| topsize5(MB):" $19 "\t\t| index:" $17 }'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select * from monitor_db_sql order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| 0-20ms:" $1 "\t\t\t| 20-100ms:" $3 "\t\t\t| 100-500ms:" $5 "\t\t\t| 500-1000ms:" $7 "\t\t\t| >1000ms:" $9}'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select * from monitor_db_log order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| totalLogSize:" $1 "\t\t| currentSize:" $3}'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select * from monitor_db_xlog order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| totalXlogSize:" $1 "\t\t| xlogCount:" $3 "\t\t\t| addition:" $5 "\t\t\t| currentLoc:" $7 "\n| replayLoc1:" $9 "\t\t| replayLoc2:" $11 "\t\t| replayLoc3:" $13 "\t\t| replayLoc4:" $15 "\n| replayLoc5:" $17 "\t\t| replayLoc6:" $19 "\t\t| replayLoc7:" $21 "\t\t| replayLoc8:" $23}'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    gsql -d postgres -p 12347 -c "select * from monitor_db_mem order by collect_time desc limit 1;" | grep -v "collect_time\|--\|row" | grep [0-9] | awk '{print "| maxDynamic(MB):" $1 "\t\t| usedDynamic(MB):" $3 "\t\t| maxShared(MB):" $5 "\t\t| usedShared(MB):" $7 "\n| topsize1(MB):" $11 "\t\t| context:" $9 "\n| topsize2(MB):" $15 "\t\t| context:" $13 "\n| topsize3(MB):" $19 "\t\t| context:" $17 "\n| topsize4(MB):" $23 "\t\t| context:" $21 "\n| topsize5(MB):" $27 "\t\t| context:" $25 }'
    echo -e "\033[46;37m----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\033[0m"
    return 0
}

#工具入口
main "$@"

